AlchemyJ by Axisoft

5.0

ajJSONFromSchema function

Available since AlchemyJ v4.0

Description

The ajJSONFromSchema function returns a JSON string based on a Data Relationship Schema. If there are multiple tables with dependency, the JSON will contain multiple levels. If a data dictionary is defined in the given data relationship schema, it will be used for data type conversion.

Syntax

ajJSONFromSchema(data_relationship_header, data_relationship)

Argument NameArgument TypeDescription
data_relationship_header
(required)		Range / ArrayThe range which defines the header of the Data Relationship Schema.
data_relationship
(required)		Range / ArrayThe range which defines the content of the Data Relationship Schema. The empty row would be ignored.
special_value_as_empty
(optional)		BooleanIf it equals TRUE, to enable treating specified values as empty string (applies to string fields only). If it equals FALSE, same as before logic, keep the empty string.
The default value is FALSE.

special_value
(optional)		StringIt is invalid when special_value_as_empty is TRUE, it will replace the empty string to the special_value to the table.

The function will return:

1) Return Value: JSON string

2) Return Type: Single Value / Multiple values (array formula). It split the returned JSON string into multiple cells when the string length is longer than a single cell character limit - 32767. The remaining string will be extended in the row array.

Example

Assume there are two tables, Customer and Email. These data can come from file (can use ajReadFile or ajReadWorkbook to read data from file), database tables (can use ajDBReadRecord to read record from database)

Add the Data Relationship Schema Snippet Insert Snippet -> Data Relationship Schema.

Note that before you start using ajJSONFromSchema function, you need to understand the usage of Data Relationship Schema.

Example 1

Define the Data Relationship as below. Table Address should be used ajAddress function to get the Customer and Email table address. It is no any structure and data type required, all field value type are string.

Apply ajJSONFromSchema function, return the five array object for customer and email table and no related relationship.

Note:

The range of AlchemyJ_Snippet_Data_Relationship_Schema_tbl[#Headers] and AlchemyJ_Snippet_Data_Relationship_Schema_tbl is A17:L17 and A18:L20.

Returned JSON:

{
    "customerList": [
        {
            "Name": "CHAN TAI MAN",
            "Date of Birth": 28127,
            "Gender": "M",
            "Customer Level": 5,
            "Telephone": "22205555",
            "Updated Datetime": 45544.4316435185
        },
        {
            "Name": "JERRY SZE",
            "Date of Birth": 32239,
            "Gender": "M",
            "Customer Level": 2,
            "Telephone": "22203333",
            "Updated Datetime": 45544.4316435185
        },
        {
            "Name": "MERRY SO",
            "Date of Birth": 32205,
            "Gender": "F",
            "Customer Level": 4,
            "Telephone": "22201111",
            "Updated Datetime": 45544.4316435185
        },
        {
            "Name": "TOM HUI",
            "Date of Birth": 28827,
            "Gender": "M",
            "Customer Level": 1,
            "Telephone": "22204444",
            "Updated Datetime": 45544.4316435185
        },
        {
            "Name": "VICKY CHAN",
            "Date of Birth": 29718,
            "Gender": "F",
            "Customer Level": 3,
            "Telephone": "22202222",
            "Updated Datetime": 45544.4316435185
        }
    ],
    "emailList": [
        {
            "Name": "CHAN TAI MAN",
            "Email": "CHAN.TAI.MAN@hotmail.com",
            "Type": "Work"
        },
        {
            "Name": "CHAN TAI MAN",
            "Email": "CHAN.TAI.MAN@gmail.com",
            "Type": "Personal"
        },
        {
            "Name": "JERRY SZE",
            "Email": "JERRY.SZE@yahoo.com",
            "Type": "Personal"
        },
        {
            "Name": "JERRY SZE",
            "Email": "JERRY.SZE@axisoft.com",
            "Type": "Work"
        },
        {
            "Name": "JESS LU",
            "Email": "JESS.LU@gmail.com",
            "Type": "Work"
        }
    ]
}

Example 2

Set parent-child relationship and data type formatting. Define Customer table as the parent table, Email table as the child table and Name field as the key. (If it hard to set the key, can use the ajJSONKeyGeneration to generate the key). And add the Data Dictionary Address to control the data format and type.

  1. Add Data Dictionary and set data type for all fields.

  2. In this case the Updated Datetime field type is datetime, it need to do a special config in the Data Dictionary sheet.

    For the data type is datetime, need set the Data Type is Date then in the Date Format list it is no datetime format, so we need to set it manually.

  3. Setting the Parent Table to Customer and the Key to Name, then apply the data dictionary to Data Relationship Schema with ajAddress function.

    Note: Customer table and Email table with same field is Name, so using the Name as joining key.

  4. Apply ajJSONFromSchema function, the parent-child relationship for Customer and Email table shown in the JSON. The field 'Date of Birth' format and 'Updated Datetime' format in the JSON same as that in the table. And if want to keep the empty value in the JSON, can use special_value_as_empty and special_value to apply it.

    Note: If a Customer records cannot find the email record by joining key, only Customer level information will appear in the JSON.

Returned JSON:

{
    "customerList": [
        {
            "Name": "CHAN TAI MAN",
            "Date of Birth": "1977-01-02",
            "Gender": "",
            "Customer Level": 5,
            "Telephone": "22205555",
            "Updated Datetime": "2024-09-09 10:21:34",
            "emailList": [
                {
                    "Email": "CHAN.TAI.MAN@hotmail.com",
                    "Type": "Work"
                },
                {
                    "Email": "CHAN.TAI.MAN@gmail.com",
                    "Type": "Personal"
                }
            ]
        },
        {
            "Name": "JERRY SZE",
            "Date of Birth": "1988-04-06",
            "Gender": "M",
            "Customer Level": 2,
            "Telephone": "22203333",
            "Updated Datetime": "2024-09-09 10:21:34",
            "emailList": [
                {
                    "Email": "JERRY.SZE@yahoo.com",
                    "Type": "Personal"
                },
                {
                    "Email": "JERRY.SZE@axisoft.com",
                    "Type": "Work"
                }
            ]
        },
        {
            "Name": "MERRY SO",
            "Date of Birth": "1988-03-03",
            "Gender": "F",
            "Customer Level": 4,
            "Telephone": "22201111",
            "Updated Datetime": "2024-09-09 10:21:34"
        },
        {
            "Name": "TOM HUI",
            "Date of Birth": "1978-12-03",
            "Gender": "M",
            "Customer Level": 1,
            "Telephone": "22204444",
            "Updated Datetime": "2024-09-09 10:21:34"
        },
        {
            "Name": "VICKY CHAN",
            "Date of Birth": "1981-05-12",
            "Gender": "F",
            "Customer Level": 3,
            "Telephone": "22202222",
            "Updated Datetime": "2024-09-09 10:21:34"
        }
    ]
}

​

Example 3

In this example, Multiple Row is set as FALSE and then only the first row in the Email table would appear in the JSON; Exclude Columns is set as Date of Birth and Telephone use "-||-" as the parameter separator then they would not appear in the JSON.

Apply ajJSONFromSchema function.

Returned JSON:

{
    "Customers": [
        {
            "Name": "CHAN TAI MAN",
            "Gender": "M",
            "Customer Level": 5,
            "Updated Datetime": "2024-09-09 10:21:34",
            "Emails": {
                "Email": "CHAN.TAI.MAN@hotmail.com",
                "Type": "Work"
            }
        },
        {
            "Name": "JERRY SZE",
            "Gender": "M",
            "Customer Level": 2,
            "Updated Datetime": "2024-09-09 10:21:34",
            "Emails": {
                "Email": "JERRY.SZE@yahoo.com",
                "Type": "Personal"
            }
        },
        {
            "Name": "MERRY SO",
            "Gender": "F",
            "Customer Level": 4,
            "Updated Datetime": "2024-09-09 10:21:34"
        },
        {
            "Name": "TOM HUI",
            "Gender": "M",
            "Customer Level": 1,
            "Updated Datetime": "2024-09-09 10:21:34"
        },
        {
            "Name": "VICKY CHAN",
            "Gender": "F",
            "Customer Level": 3,
            "Updated Datetime": "2024-09-09 10:21:34"
        }
    ]
}

Note: If you want to convert the JSON back to the table, you can use ajJSONToTable to converts JSON string into a table.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Does not use ajAddress in Table address or Data dictionary address.
Duplicate table name in the data relationship table.
For REST API, the root table was configured with Multiple Row = TRUE.
Invalid table name, it shall begin with an uppercase letter and contain alphabets, numbers, and underscores only.
Key is not provided but parent table contain multiple row.
Parent table does not exist.
Parent table name is empty but key is not empty.
Table address is empty.
Table is empty.
Table name is a reserved Java keyword. For example, new, return, default etc.
Data relationship header range is invalid, it must contain at least one column.
Data relationship range is invalid, it must contain at least one column.
← ajJSONajJSONFromTable →